# -*- coding: utf-8 -*-
#
# @Author  : Yang
# @Email  : yangjiaxian@ibbd.net
# @Time    : 2022/11/14
import datetime
import json
import os
from typing import Optional
import pandas as pd

from fastapi import APIRouter, Path, Query, Depends, File, UploadFile
from fastapi.responses import FileResponse
from openpyxl import load_workbook

from src.common.logger import logger
from settings import settings
from src.api.v1.customer.schemas import CreateCustomerParams, GetRemindCustomersCountResp, \
    CreateCustomerResp, FetchCustomersResp
from src.const import CustomerType, RemindType, Gender, Permissions, DATE_REGEX, BusinessName, DelegateState
from src.error import InternalException, status
from src.services.customer import CustomerServices
from src.services.log import LogServices
from src.utils import output_json, check_operator, get_now_time_str, modify_excel_format

customer_app = APIRouter(tags=["客户"])


@customer_app.post("/customers", summary="新增客户", response_model=CreateCustomerResp)
async def create_customers_api(
        params: CreateCustomerParams,
        operator_id: int = Depends(check_operator)
):
    if params.private_placement_strategy:
        params.private_placement_strategy = json.dumps(params.private_placement_strategy.dict())
    if params.fund_demand:
        params.fund_demand = json.dumps(params.fund_demand.dict())
    if params.technical_demand:
        params.technical_demand = json.dumps(params.technical_demand.dict())
    if params.bond_source_demand:
        params.bond_source_demand = json.dumps(params.bond_source_demand.dict())
    if params.investment_research_demand:
        params.investment_research_demand = json.dumps(params.investment_research_demand.dict())

    # 个人客户
    if params.customer_type.value == CustomerType.individual_customer.value:
        if params.gender is None:
            raise InternalException(status.HTTP_422_UNPROCESSABLE_ENTITY, message="缺少性别参数")
        if params.assignmenter:
            if not params.developer:
                raise InternalException(status.HTTP_422_UNPROCESSABLE_ENTITY, message="缺少开发关系参数")

        # 清空这些机构客户选项
        params.scale_of_management = None
        params.private_placement_strategy = None
        params.fund_demand = None
        params.technical_demand = None
        params.bond_source_demand = None
        params.investment_research_demand = None
    # 机构客户
    if params.customer_type.value == CustomerType.institutional_customer.value:
        if not params.contact_person:
            raise InternalException(status.HTTP_422_UNPROCESSABLE_ENTITY, message="缺少联系人参数")
    # 处理枚举类型
    params.customer_type = params.customer_type.value
    params.gender = params.gender.value
    customer_id = CustomerServices().create(**params.dict())

    LogServices().create(operator_id, f"新增了ID为{customer_id}的客户", params.dict())
    return output_json(data={"customer_id": customer_id}, message="")


@customer_app.put(path="/customers/{customerId}", summary="修改客户信息", response_model=CreateCustomerResp)
async def update_customer_api(
        params: CreateCustomerParams,
        customerId: int = Path(..., title="客户ID", description="客户ID"),
        operator_id: int = Depends(check_operator)
):
    # 检查客户ID是否存在
    if not CustomerServices().fetch_one(customerId):
        raise InternalException(status.HTTP_601_ID_NOT_EXIST, message="客户ID不存在")

    if params.private_placement_strategy:
        params.private_placement_strategy = json.dumps(params.private_placement_strategy.dict())
    if params.fund_demand:
        params.fund_demand = json.dumps(params.fund_demand.dict())
    if params.technical_demand:
        params.technical_demand = json.dumps(params.technical_demand.dict())
    if params.bond_source_demand:
        params.bond_source_demand = json.dumps(params.bond_source_demand.dict())
    if params.investment_research_demand:
        params.investment_research_demand = json.dumps(params.investment_research_demand.dict())

    # 个人客户
    if params.customer_type.value == CustomerType.individual_customer.value:
        if params.gender is None:
            raise InternalException(status.HTTP_422_UNPROCESSABLE_ENTITY, message="缺少性别参数")
        if params.assignmenter:
            if not params.developer:
                raise InternalException(status.HTTP_422_UNPROCESSABLE_ENTITY, message="缺少开发关系参数")
        # 清空这些机构客户选项
        params.scale_of_management = None
        params.private_placement_strategy = None
        params.fund_demand = None
        params.technical_demand = None
        params.bond_source_demand = None
        params.investment_research_demand = None
    # 机构客户
    if params.customer_type.value == CustomerType.institutional_customer.value:
        if not params.contact_person:
            raise InternalException(status.HTTP_422_UNPROCESSABLE_ENTITY, message="缺少联系人参数")
    # 处理枚举类型
    params.customer_type = params.customer_type.value
    params.gender = params.gender.value

    CustomerServices().update(customerId, **params.dict())

    LogServices().create(operator_id, f"修改了ID为{customerId}的客户", params.dict())
    return output_json(data={"customer_id": customerId}, message="")


@customer_app.delete(path="/customers/{customerId}", summary="删除客户", response_model=CreateCustomerResp)
async def delete_customer_api(
        customerId: int = Path(..., title="客户ID", description="客户ID"),
        operator_id: int = Depends(check_operator)
):
    # 检查客户ID是否存在
    if not CustomerServices().fetch_one(customerId):
        raise InternalException(status.HTTP_601_ID_NOT_EXIST, message="客户ID不存在")

    CustomerServices().delete(customerId)

    LogServices().create(operator_id, f"删除了ID为{customerId}的客户")
    return output_json(data={"customer_id": customerId}, message="删除客户成功")


@customer_app.get(path="/customers/remind/count", summary="获取提醒的客户数量", response_model=GetRemindCustomersCountResp)
async def get_remind_customers_count_api(
        operator_id: int = Depends(check_operator)
):
    today = str(datetime.datetime.today().date())
    data = CustomerServices().get_remind_customers_count(today)
    return output_json(data=data, message="")


@customer_app.get(path="/customers", summary="查询客户", response_model=FetchCustomersResp)
async def fetch_customers_api(
        customer_id: int = Query(None, title="客户ID", description="客户ID"),
        capital_account: str = Query(None, title="资金账号", description="资金账号"),
        customer_type: Optional[CustomerType] = Query(None, title="客户类型", description="客户类型"),
        gender: Optional[Gender] = Query(None, title="性别", description="0是男性，1是女性"),
        name: str = Query(None, title="名称", description="名称/名字"),
        contact_person: str = Query(None, title="联系人", description="联系人（人名）"),
        phone: str = Query(None, title="联系方式（手机号码）", description="联系方式（手机号码）"),
        developer: str = Query(None, title="开发关系（开发人）", description="开发关系（开发人）"),
        assignmenter: str = Query(None, title="服务包分配", description="服务包分配"),
        follower: str = Query(None, title="跟进情况", description="跟进情况"),
        is_internet_channel: bool = Query(None, title="是否是互联网渠道", description="是否是互联网渠道"),
        permissions: Optional[Permissions] = Query(None, title="权限管理", description="权限管理"),
        permissions_on_state: bool = Query(True, title="权限状态", description="True表示权限开启"),
        margin_account: str = Query(None, title="融资融券账号", description="融资融券账号"),
        remind_type: Optional[RemindType] = Query(None, title="提醒的类型", description="提醒的类型, 不填就不过滤"),
        fund_name: str = Query(None, title='基金名字模糊查询', description='基金名字模糊查询'),
        business_name: Optional[BusinessName] = Query(None, title='业务名称', description="业务名称"),
        delegate_state: Optional[DelegateState] = Query(None, title='委托状态', description="委托状态"),
        fund_type: str = Query(None, title='基金类型', description="基金类型"),
        buy_date: str = Query(None, regex=DATE_REGEX, title="购买时间", description="购买时间"),

        pageNo: int = Query(1, ge=0, title="页码", description="页码"),
        pageSize: int = Query(20, ge=0, title="页大小", description="特殊：返回PageSize -1条数据"),
        operator_id: int = Depends(check_operator)
):
    """
    提醒类型（三种）：优惠利率即将失效客户、基金到期客户、需要联系的客户\n
    说明：这种提醒类型配合客户类型查询出符合条件的客户
    """
    if customer_type is not None:
        customer_type = customer_type.value
    if gender is not None:
        gender = gender.value
    total, data, all_fund_summary = CustomerServices().fetch_data(
        remind_type, customer_id, capital_account, customer_type, name, contact_person, phone, developer, assignmenter,
        is_internet_channel, follower, margin_account, gender, permissions, fund_name, permissions_on_state,
        business_name,
        delegate_state, fund_type, buy_date, pageNo, pageSize - 1)

    summary = {
        "id": "--",
        "capital_account": "--",
        "customer_type": "--",
        "name": "汇总",
        "gender": "--",
        "contact_person": "--",
        "phone": "--",
        "occupation": "--",
        "birthday": "--",
        "certificate_type": "--",
        "certificate_number": "--",
        "existing_assets": "--",
        "historical_peak": "--",
        "customer_source": "--",
        "specific_channel": "--",
        "commission_rate": "--",
        "risk_appetite": "--",
        "contact_status": "--",
        "developer": "--",
        "is_internet_channel": "--",
        "assignmenter": "--",
        "follower": "--",
        "cash_treasure": "--",
        "automatic_investment_plan": "--",
        "double_innovation_board": "--",
        "share_option": "--",
        "shenzhen_hong_kong_stock_connect": "--",
        "shanghai_hong_kong_stock_connect": "--",
        "double_margin_account": "--",
        "beijing_stock_exchange": "--",
        "pension_account": "--",
        "margin_account": "--",
        "account_opening_date": "--",
        "preferential_interest_rate": "--",
        "interest_rate_effective_date": "--",
        "interest_rate_expiry_date": "--",
        "interest_rate_expiry_remind_date": "--",
        "remark": "--",
        "operator_id": "--",
        "scale_of_management": "--",
        "private_placement_strategy": "--",
        "fund_demand": "--",
        "technical_demand": "--",
        "bond_source_demand": "--",
        "investment_research_demand": "--",
        "is_delete": "--",
        "created_at": "--",
        "updated_at": "--",
        "fund_amount_summary": round(all_fund_summary, 2)
    }
    return output_json(data=data, message='', total=total, summary=summary)


@customer_app.get(path="/customers/{customerId}", summary="获取单个客户信息")
async def fetch_customer_api(
        customerId: int = Path(..., title="客户ID", description="客户ID"),
        operator_id: int = Depends(check_operator)
):
    """
    """
    data = CustomerServices().fetch_one(customerId)
    if not data:
        raise InternalException(status.HTTP_601_ID_NOT_EXIST, message="客户不存在")
    return output_json(data=data, message='')


@customer_app.post(path="/customers/import", summary="批量导入客户数据接口", tags=["NEW!"])
async def import_customer_api(
        operator_id: int = Depends(check_operator),
        customer_file: UploadFile = File(..., title="客户数据", description="客户数据")
):
    customer_content = await customer_file.read()
    filename = customer_file.filename
    filename_suffix = filename.split('.')[-1]
    if filename_suffix not in ["xlsx"]:  # openpyxl只支持xlsx后缀文件
        raise InternalException(status.HTTP_623_FILE_ERROR, message="文件格式不对")

    with open(os.path.join(settings.FILES_DIR, filename), "wb") as f:
        f.write(customer_content)

    # 打开Excel文件
    workbook = load_workbook(os.path.join(settings.FILES_DIR, filename))
    # 选择第一个工作表
    sheet = workbook.active
    # 创建一个空列表，用于存储行数据
    rows_data = []
    # 迭代每一行并将其添加到列表中
    for index, row in enumerate(sheet.iter_rows()):
        row_data = [index]
        for cell in row:
            row_data.append(cell.value)
        rows_data.append(row_data)

    failure_data = []
    count = 0
    for data in rows_data[1:]:
        customer_data = {
            "capital_account": data[1],
            "name": data[2],
            "certificate_type": data[3],
            "certificate_number": data[4],
            "historical_peak": data[5],
            "phone": data[6],
            "occupation": data[7],
            "developer": data[8],
            "assignmenter": data[9],
            "follower": data[10],
            "customer_type": 0 if data[11] == "个人客户" else 1,
            "operator_id": operator_id
        }
        try:
            customer_id = CustomerServices().create(**customer_data)
            LogServices().create(operator_id, f"新增了ID为{customer_id}的客户", customer_data)
            if customer_id:
                count += 1
        except Exception as e:
            logger.error(str(e))
            customer_data["index"] = data[0]
            customer_data["failure_reason"] = str(e)
            failure_data.append(customer_data)

    message = f'成功导入{count}条数据，失败{len(failure_data)}条数据'
    if failure_data:
        message += f'，其中失败的数据编号为{",".join([str(item["index"]) for item in failure_data])}'

    return output_json(data=None, message=message, failure_data=failure_data)


@customer_app.get(path="/funds/export", summary="导出客户的基金数据", tags=["NEW!"])
async def export_customers_funds_data_api(
        customer_id: Optional[int] = Query(None, title="客户ID", description="客户ID"),
        capital_account: str = Query(None, title="资金账号", description="资金账号"),
        customer_type: Optional[CustomerType] = Query(None, title="客户类型", description="客户类型"),
        gender: Optional[Gender] = Query(None, title="性别", description="0是男性，1是女性"),
        name: str = Query(None, title="名称", description="名称/名字"),
        contact_person: str = Query(None, title="联系人", description="联系人（人名）"),
        phone: str = Query(None, title="联系方式（手机号码）", description="联系方式（手机号码）"),
        developer: str = Query(None, title="开发关系（开发人）", description="开发关系（开发人）"),
        assignmenter: str = Query(None, title="服务包分配", description="服务包分配"),
        follower: str = Query(None, title="跟进情况", description="跟进情况"),
        is_internet_channel: bool = Query(None, title="是否是互联网渠道", description="是否是互联网渠道"),
        permissions: Optional[Permissions] = Query(None, title="权限管理", description="权限管理"),
        permissions_on_state: bool = Query(True, title="权限状态", description="True表示权限开启"),
        margin_account: str = Query(None, title="融资融券账号", description="融资融券账号"),
        remind_type: Optional[RemindType] = Query(None, title="提醒的类型", description="提醒的类型, 不填就不过滤"),
        fund_name: str = Query(None, title='基金名字模糊查询', description='基金名字模糊查询'),
        business_name: Optional[BusinessName] = Query(None, title='业务名称', description="业务名称"),
        delegate_state: Optional[DelegateState] = Query(None, title='委托状态', description="委托状态"),
        fund_type: str = Query(None, title='基金类型', description="基金类型"),
        buy_date: str = Query(None, regex=DATE_REGEX, title="购买时间", description="购买时间"),
        operator_id: int = Depends(check_operator)
):
    if customer_type is not None:
        customer_type = customer_type.value
    if gender is not None:
        gender = gender.value

    data = CustomerServices().get_export_customer_funds(
        remind_type, customer_id, capital_account, customer_type, name, contact_person, phone, developer, assignmenter,
        is_internet_channel, follower, margin_account, gender, permissions, fund_name, permissions_on_state,
        business_name, delegate_state, fund_type, buy_date
    )

    filename = get_now_time_str() + ".xlsx"
    filepath = os.path.join(settings.FILES_DIR, filename)
    columns = ["客户号", "客户姓名", "销售人", "基金名称", "基金代码", "基金公司名称", "业务名称", "委托状态", "交易日期", "确认金额", "基金类型"]

    excel_data = [columns]
    excel_data.extend(
        [
            row["capital_account"], row["customer_name"], row["salesman"], row["name"], row["fund_code"],
            row["company_name"], row["business_name"], row["delegate_state"], row["buy_date"], row["amount"],
            row["fund_type"]
        ] for row in data)

    data_df = pd.DataFrame(data=excel_data, columns=columns)
    with pd.ExcelWriter(path=filepath, engine="xlsxwriter") as writer:
        data_df.to_excel(excel_writer=writer, sheet_name="Sheet1", header=False, index=False, startcol=0, startrow=1)
        modify_excel_format(excel_data, writer, data_df)
        writer.save()
        # writer.close()

    return FileResponse(filepath, filename=filename)
